import re
import shutil
import glob
import os
from pathlib import Path
import openpyxl


def remove_speical(s):
    return re.sub(r'[^\w_.-]', '_', s)


def rename_excel(excel_file, problem_files_folder):
    try:
        with open(excel_file, "r") as file:
            file.close()
    except IOError:
        print(f"{excel_file} 正被占用无法操作，跳过")
        return
    try:
        wb = openpyxl.load_workbook(excel_file, data_only=True, read_only=True)
        ws = wb.active
        # for cell in ws[2]:
        #     print(cell.value)
        # 2是行
        # 3到6是列
        name = '_'.join([str(x.value) for x in ws[2][3:6]])
        name = remove_speical(name)
        # print(name)
        excel_path = Path(excel_file)
        folder = excel_path.parent
        des_file = folder / (name + '.xlsx')
        print(f"{excel_path} --> {des_file}")
        wb.close()
        # 运行检查文件名没问题再开启
        if not des_file.is_file():
            shutil.move(excel_file, des_file)
    except Exception as e:
        # 遇到问题文件，则输出文件名和错误原因，复制文件到指定文件夹以便调查，继续处理其他文件
        print(excel_file, e)
        shutil.copy2(excel_file, problem_files_folder)


if __name__ == '__main__':
    excel_files_folder = r"D:\dl\dfs"
    problem_files_folder = r"D:\dl\dfs\prblems"
    os.makedirs(problem_files_folder, exist_ok=True)
    for data_file in glob.glob(rf'{excel_files_folder}\**\*.xlsx', recursive=True):
        rename_excel(data_file, problem_files_folder)
